package com.example.bookshop.dao;

import com.example.bookshop.bean.BookTypeBean;
import com.example.bookshop.bean.PageBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Component
public class BookTypeDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 添加图书类型
     * @param type
     * @return
     */
    public int insertType(BookTypeBean type){
        String sql = "INSERT INTO book_type(type_name,type_depict) VALUES(?,?)";
        return jdbcTemplate.update(sql,type.getTypeName(),type.getTypeDepict());
    }

    /**
     * 查询所有类型
     * 查找类型
     *
     * @return {@link List}<{@link BookTypeBean}>
     */
    public List<BookTypeBean> selectTypes(){
        String sql = "SELECT type_id, type_name,type_depict FROM book_type";
        List<BookTypeBean> types = new ArrayList<>();
        types = jdbcTemplate.query(sql, new RowMapper<BookTypeBean>() {

            @Override
            public BookTypeBean mapRow(ResultSet rs, int rowNum) throws SQLException {
                BookTypeBean type = new BookTypeBean();
                type.setTypeId(rs.getInt("type_id"));
                type.setTypeName(rs.getString("type_name"));
                type.setTypeDepict(rs.getString("type_depict"));
                return type;
            }
        });
        return types;
    }

    /**
     * 选择类型分页信息
     *
     * @param page 页面
     * @return {@link List}<{@link BookTypeBean}>
     */
    public List<BookTypeBean> selectTypesPage(PageBean page){
        String sql = "SELECT type_id, type_name,type_depict FROM book_type LIMIT ?,?";
        List<BookTypeBean> types = new ArrayList<>();
        types = jdbcTemplate.query(sql, new RowMapper<BookTypeBean>() {

            @Override
            public BookTypeBean mapRow(ResultSet rs, int rowNum) throws SQLException {
                BookTypeBean type = new BookTypeBean();
                type.setTypeId(rs.getInt("type_id"));
                type.setTypeName(rs.getString("type_name"));
                type.setTypeDepict(rs.getString("type_depict"));
                return type;
            }
        },(page.getPageNow()-1)*page.getPageSize(),page.getPageSize());
        return types;
    }

    /**
     * 查询总行数
     * @return
     */
    public int getRowCount(){
        String sql="SELECT COUNT(type_id) FROM book_type";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }


    /**
     * 删除类型
     *
     * @param id id
     * @return int
     */
    public int deleteType(Integer id) {
        String sql = "DELETE FROM book_type WHERE type_id=?";
        //String sql2="SET @auto_id = 0;UPDATE book_type SET type_id = ( @auto_id := @auto_id + 1 );" +
                //"ALTER TABLE book_type AUTO_INCREMENT = 1;";
        int update = jdbcTemplate.update(sql,id);
        //jdbcTemplate.update(sql2);
        return update;
    }

    /**
     * 更新类型
     *
     * @param type 类型
     * @return int
     */
    public int updateType(BookTypeBean type){
        String sql = "UPDATE book_type SET  type_name=?,type_depict=? WHERE type_id=?";
        return jdbcTemplate.update(sql,type.getTypeName(),type.getTypeDepict(),type.getTypeId());
    }
}
